﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace InsertProductIntoNorthwind
{
    public class Program
    {
        public static void AddProduct(string productName, int? supplierID, int? categoryID, 
            string quantityPerUnit, decimal? unitPrice, int? unitsInStock, int? unitsOnOrder,
            int? reorderLevel, bool discontinued)
        {
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
            "Database=Northwind; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand insertCommand = new SqlCommand(
                    "INSERT INTO Products (ProductName, SupplierID, CategoryID, " +
                        "QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, " +
                        "ReorderLevel, Discontinued) " +
                    "VALUES (@ProductName, @SupplierID, @CategoryID, " +
                        "@QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, " +
                        "@ReorderLevel, @Discontinued)", dbCon);
                insertCommand.Parameters.Add(new SqlParameter("@ProductName", productName));
                insertCommand.Parameters.Add(new SqlParameter("@SupplierID", dbValue(supplierID)));
                insertCommand.Parameters.Add(new SqlParameter("@CategoryID", dbValue(categoryID)));
                insertCommand.Parameters.Add(new SqlParameter("@QuantityPerUnit", dbValue(quantityPerUnit)));
                insertCommand.Parameters.Add(new SqlParameter("@UnitPrice", dbValue(unitPrice)));
                insertCommand.Parameters.Add(new SqlParameter("@UnitsInStock", dbValue(unitsInStock)));
                insertCommand.Parameters.Add(new SqlParameter("@UnitsOnOrder", dbValue(unitsOnOrder)));
                insertCommand.Parameters.Add(new SqlParameter("@ReorderLevel", dbValue(reorderLevel)));
                insertCommand.Parameters.Add(new SqlParameter("@Discontinued", discontinued));

                int rows = insertCommand.ExecuteNonQuery();
                Console.WriteLine("{0} row{1} affected.", rows, rows == 1 ? "" : "s");
            }
        }

        private static object dbValue(object obj)
        {
            if (obj == null)
            {
                return DBNull.Value;
            }
            else
            {
                return obj;
            }
        }

        public static void Main()
        {
            AddProduct("Dummy product", null, 1, null, null, null, null, 23, true);
        }
    }
}
